﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;
using Ortodoctor.Componentes;

namespace Ortodoctor.modelo
{
    class modeloDentista : conexao
    {
        public modeloDentista()
        {
            conn = new NpgsqlConnection(connectionstring);
            conn.Open();
        }

        public void insert(Dentista d)
        {
            try
            {
                //con.open();
                String sql = "INSERT INTO dentista(id_clinica, id_funcionario) VALUES (:p1, :p2)";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", d.Clinica.IdClinica);
                com.Parameters.AddWithValue("p2", d.Funcionario.IdFuncionario);
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public DataTable getAll()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                //String sql = "SELECT * FROM dentista INNER JOIN clinica USING (id_clinica) " +
                //             "INNER JOIN funcionario using (id_funcionario)";
                String sql = "SELECT * FROM funcionario INNER JOIN clinica USING (id_clinica) ";
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public DataTable getAll(int id_clinica)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT * FROM FUNCIONARIO INNER JOIN CLINICA USING (ID_CLINICA) " + 
                                    "INNER JOIN ENDERECO USING(ID_ENDERECO) WHERE FUNCIONARIO.ID_CLINICA = " + id_clinica;
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public DataTable getDentista(int id)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT * FROM funcionario INNER JOIN clinica USING (id_clinica) WHERE id_funcionario=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public void update(Dentista d)
        {
            try
            {
                //con.open();
                String sql = "UPDATE dentista SET id_clinica=:p1, id_funcionario=:p2, id_usuario_modificacao=:p4 where id_dentista = :p3";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", d.Clinica.IdClinica);
                com.Parameters.AddWithValue("p2", d.Funcionario.IdFuncionario);
                com.Parameters.AddWithValue("p3", d.IdDentista);
                com.Parameters.AddWithValue("p4", System.Web.HttpContext.Current.Session["ID_USUARIO_ACESSO"]);
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public void delete(int id)
        {
            try
            {
                //con.open();
                String sql = "DELETE FROM dentista WHERE id_dentista=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                com.ExecuteNonQuery();

                sql = "UPDATE dentista_hist SET data=:p1 id_usuario_modificacao=:p3 where id_dentista = :p2";
                com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss"));
                com.Parameters.AddWithValue("p2", id);
                com.Parameters.AddWithValue("p3", System.Web.HttpContext.Current.Session["ID_USUARIO_ACESSO"]);
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
